﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using DailyReport.Models;
using MyHelpers;
using MySql.Data.MySqlClient;
using MySqlHelper = MyHelpers.MySqlHelper;

namespace DailyReport.Helpers
{
    public sealed class DrDbHelper
    {
        /// <summary>
        /// 用户名称-ID缓存字典，用于减少数据库查询。
        /// </summary>
        /// <remarks>因用户名唯一且非空，故该字典在理论上是安全的</remarks>
        private static Dictionary<string, string> _dicUserNameId; 

        static DrDbHelper()
        {
            MySqlHelper.ConnStr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;

            _dicUserNameId = new Dictionary<string, string>();
        }

        // ReSharper disable once EmptyConstructor
        public DrDbHelper()
        {
            
        }

        #region 登录表

        /// <summary>判断用户账户是否已存在</summary>
        /// <param name="name">用户账户名</param>
        /// <returns></returns>
        public bool ExistUser(string name)
        {
            if (string.IsNullOrEmpty(name)) return false;

            string sql = string.Format(@"select count(*) from DR_USER where NAME = '{0}'", name);
            var obj = MySqlHelper.GetSingle(sql);
            int res;
            if (obj == null || obj is DBNull || !int.TryParse(obj.ToString(), out res)) return false;
            return res > 0;
        }

        /// <summary>添加用户登录信息</summary>
        /// <param name="name">用户名</param>
        /// <param name="ipAddress">IP地址</param>
        /// <returns></returns>
        public string AddLoginInfo(string name, string ipAddress)
        {
            if (string.IsNullOrEmpty(ipAddress)) ipAddress = string.Empty;

            var sql = "INSERT INTO DR_LOGININFO (NAME,LOGIN_TIME,LOGIN_IP) VALUES (@NAME,@LOGIN_TIME,@LOGIN_IP)";
            MySqlParameter[] pars =
            {
                new MySqlParameter("NAME", MySqlDbType.VarChar),
                new MySqlParameter("LOGIN_TIME", MySqlDbType.Timestamp),
                new MySqlParameter("LOGIN_IP", MySqlDbType.VarChar)
            };
            pars[0].Value = name;
            pars[1].Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            pars[2].Value = ipAddress;

            return MySqlHelper.ExecuteSql(sql, pars) > 0 ? "Success" : "Failed";
        }


        /// <summary>添加用户</summary>
        /// <param name="name">用户名</param>
        /// <param name="pass">密码</param>
        /// <param name="isAdmin">是否为系统管理员</param>
        /// <param name="ipAddress">IP地址</param>
        /// <param name="email">Email地址</param>
        /// <returns></returns>
        public string AddUser(string name, string pass, bool isAdmin, string ipAddress, string email)
        {
            if (string.IsNullOrEmpty(name) || string.IsNullOrEmpty(pass))
                return "Bad Para";
            if (name.Length > 255) return "Bad Para";
            if (pass.Length > 255) return "Bad Para";
            if (ExistUser(name)) return "Bad Para";

            string author = isAdmin ? "SN" : "N";

            var sqlBuilder = new StringBuilder("INSERT INTO DR_USER (NAME,PASS,AUTHOR,LAST_LOGIN_TIME,LAST_IP,EMAIL)");
            sqlBuilder.Append(" VALUES (@NAME,@PASS,@AUTHOR,@LAST_LOGIN_TIME,@LAST_IP,@EMAIL)");
            MySqlParameter[] pars =
            {
                new MySqlParameter("NAME", MySqlDbType.VarChar),
                new MySqlParameter("PASS", MySqlDbType.VarChar),
                new MySqlParameter("AUTHOR", MySqlDbType.VarChar),
                new MySqlParameter("LAST_LOGIN_TIME", MySqlDbType.Timestamp),
                new MySqlParameter("LAST_IP", MySqlDbType.VarChar),
                new MySqlParameter("EMAIL", MySqlDbType.VarChar)
            };
            pars[0].Value = name;
            pars[1].Value = pass;
            pars[2].Value = author;
            pars[3].Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            pars[4].Value = ipAddress;
            pars[4].Value = email;

            return MySqlHelper.ExecuteSql(sqlBuilder.ToString(), pars) > 0 ? "Success" : "Failed";
        }

        /// <summary>判断登录信息是否成功</summary>
        /// <param name="name">用户名</param>
        /// <param name="pass">密码</param>
        /// <param name="ipAddress">IP地址</param>
        /// <returns></returns>
        public string LoginUser(string name, string pass, string ipAddress)
        {
            if (string.IsNullOrEmpty(name) || string.IsNullOrEmpty(pass))
                return "Bad Para";
            if (name.Length > 255) return "Bad Para";
            if (pass.Length > 255) return "Bad Para";
            if (!ExistUser(name)) return "User Not Exist";

            string sql = string.Format(@"SELECT * FROM DR_USER WHERE NAME = '{0}'", name);
            var dt = MySqlHelper.GetTable(sql);
            if (dt == null || dt.Rows.Count == 0) return "User Not Exist";

            if (!dt.Rows[0]["PASS"].ToString().Equals(pass)) return "Wrong Pass";

            sql = string.Format(@"UPDATE DR_USER SET LAST_LOGIN_TIME='{0}',LAST_IP='{1}' WHERE NAME='{2}'",
                DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), ipAddress, name);
            MySqlHelper.ExecuteSql(sql);
            return "Success";
        }

        /// <summary>根据用户名获取用户ID</summary>
        /// <param name="name">用户名</param>
        /// <returns>用户ID，不存在返回null</returns>
        public string GetUserId(string name)
        {
            if (string.IsNullOrEmpty(name) || name.Length > 255)
                return null;

            if (_dicUserNameId.ContainsKey(name)) return _dicUserNameId[name];

            string sql = string.Format(@"SELECT * FROM DR_USER WHERE NAME = '{0}'", name);
            var dt = MySqlHelper.GetTable(sql);
            if (dt == null || dt.Rows.Count == 0) return null;

            string userId = dt.Rows[0]["ID"].ToString();
            if (!_dicUserNameId.ContainsKey(name)) _dicUserNameId.Add(name, userId);
            return userId;
        }

        /// <summary>获取Email地址</summary>
        /// <param name="name">用户名</param>
        /// <returns>用户Email地址，不存在返回null</returns>
        internal string GetEmail(string name)
        {
            if (string.IsNullOrEmpty(name) || name.Length > 255)
                return null;

            string sql = string.Format(@"SELECT EMAIL FROM DR_USER WHERE NAME = '{0}'", name);
            var email = MySqlHelper.GetSingle(sql).ToString();
            if (string.IsNullOrWhiteSpace(email)) return null;

            return email;
        }

        /// <summary>获取用户名列表</summary>
        /// <returns>用户名列表，失败返回null</returns>
        internal List<string> GetUserNames()
        {
            string sql =@"SELECT * FROM DR_USER";
            var dt = MySqlHelper.GetTable(sql);
            if (dt == null || dt.Rows.Count == 0) return null;

            return (from DataRow dr in dt.Rows select dr["NAME"].ToString()).ToList();
        }

        #endregion

        #region 日志表

        public bool AddRecord(string userName, string text, string projectName, string typeName, string remark,
            double workHour, double overtime = 0, string recDate = null, string msg = null)
        {
            if (string.IsNullOrEmpty(userName) || string.IsNullOrEmpty(text) ||
                string.IsNullOrEmpty(projectName) || string.IsNullOrEmpty(typeName))
                return false;

            string userId = GetUserId(userName);
            if (string.IsNullOrEmpty(userId)) return false;
            var dateToday = recDate ?? DateTime.Now.ToString("yyyy-MM-dd");
            var projectId = GetProjectId(projectName);
            var typeId = GetProjectTypeId(typeName);
            if (projectId < 0 || typeId < 0) return false;

            string sql =
                string.Format(
                    @"INSERT INTO DR_DAILYREPORT (ID_USER,REC_TEXT,REC_DATE,ID_PROJECT,ID_TYPE,WORK_HOUR,OVERTIME_HOUR,REMARK,MSG) VALUE ({0},'{1}','{2}',{3},{4},{5},{6},'{7}','{8}')",
                    userId, text, dateToday, projectId, typeId, workHour, overtime, remark, msg);

            return MySqlHelper.ExecuteSql(sql) > 0;
        }

        public bool AddRecords(bool delFirst, string userName, List<string> texts, List<string> projectNames, List<string> typeNames, List<string> remarks,
            List<double> workHours, List<double> overtimes, List<string> recDates, List<string> msgs = null)
        {
            if (string.IsNullOrEmpty(userName) || texts.Any(string.IsNullOrEmpty) ||
                projectNames.Any(string.IsNullOrEmpty) || typeNames.Any(string.IsNullOrEmpty))
                return false;

            string userId = GetUserId(userName);
            if (string.IsNullOrEmpty(userId)) return false;

            int count = texts.Count;
            string defDate = DateTime.Now.ToString("yyyy-MM-dd");
            List<string> sqls = new List<string>(count + 1);
            if (delFirst) sqls.Add(string.Format(@"DELETE FROM DR_DAILYREPORT WHERE ID_USER={0}", userId));
            for (int i = 0; i < count; i++)
            {
                var dateToday = recDates[i] ?? defDate;
                var projectId = GetProjectId(projectNames[i]);
                var typeId = GetProjectTypeId(typeNames[i]);
                if (projectId < 0 || typeId < 0) return false;
                var msg = msgs == null ? string.Empty : msgs[i];

                string sql =
                string.Format(
                    @"INSERT INTO DR_DAILYREPORT (ID_USER,REC_TEXT,REC_DATE,ID_PROJECT,ID_TYPE,WORK_HOUR,OVERTIME_HOUR,REMARK,MSG) VALUE ({0},'{1}','{2}',{3},{4},{5},{6},'{7}','{8}')",
                    userId, texts[i], dateToday, projectId, typeId, workHours[i], overtimes[i], remarks[i], msg);
                sqls.Add(sql);
            }

            return MySqlHelper.ExecuteSqlTran(sqls);
        }


        public bool UpdateRecord(int id, string recDate, string text, string projectName, string typeName, string remark,
            double workHour, double overtime = 0, string msg = null)
        {
            if (string.IsNullOrEmpty(projectName) || string.IsNullOrEmpty(typeName) || id < 0)
                return false;

            var projectId = GetProjectId(projectName);
            var typeId = GetProjectTypeId(typeName);
            if (projectId < 0 || typeId < 0) return false;

            string sql =
                string.Format(
                    @"UPDATE DR_DAILYREPORT SET REC_DATE='{0}',REC_TEXT='{1}',ID_PROJECT={2},ID_TYPE={3},WORK_HOUR={4},OVERTIME_HOUR={5},REMARK='{6}',MSG='{7}' WHERE ID={8}",
                    recDate, text, projectId, typeId, workHour, overtime, remark, msg, id);

            return MySqlHelper.ExecuteSql(sql) > 0;
        }


        public bool DeleteRecord(int id)
        {
            if (id < 0) return false;

            string sql = string.Format(@"DELETE FROM DR_DAILYREPORT WHERE ID={0}", id);

            return MySqlHelper.ExecuteSql(sql) > 0;
        }

        public bool DeleteAllRecords(string userName)
        {
            if (string.IsNullOrEmpty(userName))
                return false;

            string userId = GetUserId(userName);
            if (string.IsNullOrEmpty(userId)) return false;

            string sql = string.Format(@"DELETE FROM DR_DAILYREPORT WHERE ID_USER={0}",userId);

            return MySqlHelper.ExecuteSql(sql) >= 0;
        }

        public DataTable GetRecords(string userName, string startD, string endD)
        {
            DateTime startDate = DateTime.Parse(startD);
            DateTime endDate = DateTime.Parse(endD);
            return GetRecords(userName, startDate, endDate);
        }


        public DataTable GetRecords(string userName, DateTime startDate, DateTime endDate)
        {
            if (string.IsNullOrEmpty(userName) || startDate == endDate)
                return null;

            if (startDate > endDate)
            {
                DateTime dtTmp = startDate;
                startDate = endDate;
                endDate = dtTmp;
            }
            endDate = endDate.AddDays(1);   // 用户输入日期原意应为包含所选的最后一天

            string userId = GetUserId(userName);
            if (string.IsNullOrEmpty(userId)) return null;

            string startD = startDate.ToString("yyyy-MM-dd");
            string endD = endDate.ToString("yyyy-MM-dd");

            string sql =
                string.Format(@"SELECT * FROM DR_DAILYREPORT WHERE ID_USER={0} AND REC_DATE>'{1}' AND REC_DATE<'{2}' ORDER BY REC_DATE",
                    userId, startD, endD);

            var dt = MySqlHelper.GetTable(sql);
            if (dt == null || dt.Rows.Count == 0) return null;
            var dtOpt = GetOptRecordsTable(dt);

            return dtOpt;
        }


        public DataTable GetAllRecords(string userName)
        {
            if (string.IsNullOrEmpty(userName))
                return null;

            string userId = GetUserId(userName);
            if (string.IsNullOrEmpty(userId)) return null;

            string sql =
                string.Format(@"SELECT * FROM DR_DAILYREPORT WHERE ID_USER={0} ORDER BY REC_DATE", userId);

            var dt = MySqlHelper.GetTable(sql);
            if (dt == null || dt.Rows.Count == 0) return null;
            var dtOpt = GetOptRecordsTable(dt);

            return dtOpt;
        }

        DataTable GetOptRecordsTable(DataTable dt)
        {
            if (dt == null || dt.Rows.Count == 0) return null;

            var dicProjectName = GetProjectDic();
            var dicProjectType = GetProjectTypeDic();

            var dtOpt = new DataTable();
            dtOpt.Columns.Add("ID", typeof(string));
            dtOpt.Columns.Add("记录时间", typeof(string));
            dtOpt.Columns.Add("工作描述", typeof(string));
            dtOpt.Columns.Add("项目", typeof(string));
            dtOpt.Columns.Add("活动类型", typeof(string));
            dtOpt.Columns.Add("工作量", typeof(string));
            dtOpt.Columns.Add("加班量", typeof(string));
            dtOpt.Columns.Add("备注", typeof(string));
            dtOpt.AcceptChanges();
            foreach (DataRow dr in dt.Rows)
            {
                var row = dtOpt.NewRow();
                row["ID"] = dr["ID"].ToString();
                DateTime recTime = DateTime.Parse(dr["REC_DATE"].ToString());
                row["记录时间"] = recTime.ToString("yyyy-MM-dd");
                row["工作描述"] = dr["REC_TEXT"].ToString();
                row["项目"] = dicProjectName[Convert.ToInt32(dr["ID_PROJECT"])];
                row["活动类型"] = dicProjectType[Convert.ToInt32(dr["ID_TYPE"])];
                row["工作量"] = dr["WORK_HOUR"].ToString();
                row["加班量"] = dr["OVERTIME_HOUR"].ToString();
                row["备注"] = dr["REMARK"].ToString();
                dtOpt.Rows.Add(row);
            }
            dtOpt.AcceptChanges();
            return dtOpt;
        }

        #endregion

        #region 项目名称表

        /// <summary>判断项目名称是否已存在</summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public bool ExistProject(string name)
        {
            if (string.IsNullOrEmpty(name)) return false;

            string sql = string.Format(@"select count(*) from DR_PROJECTNAME where NAME = '{0}'", name);
            var obj = MySqlHelper.GetSingle(sql);
            int res;
            if (obj == null || obj is DBNull || !int.TryParse(obj.ToString(), out res)) return false;
            return res > 0;
        }

        /// <summary>根据项目名称获取ID</summary>
        /// <param name="name"></param>
        /// <param name="donnotCheckExist"></param>
        /// <returns></returns>
        /// <remarks>未找到加入项目名称，错误返回-1</remarks>
        public int GetProjectId(string name, bool donnotCheckExist = false)
        {
            if (string.IsNullOrEmpty(name)) return -1;

            if (donnotCheckExist || ExistProject(name))
            {
                string sql = string.Format(@"select ID from DR_PROJECTNAME where NAME = '{0}' limit 1", name);
                var obj = MySqlHelper.GetSingle(sql);
                int res;
                if (obj == null || obj is DBNull || !int.TryParse(obj.ToString(), out res)) return -1;
                return res;
            }
            else
            {
                string sql = string.Format(@"insert into DR_PROJECTNAME (NAME) value ('{0}')", name);
                if (MySqlHelper.ExecuteSql(sql) <= 0) return -1;

                return GetProjectId(name, true);
            }
        }

        /// <summary>添加项目名称</summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public bool AddProject(string name)
        {
            if (string.IsNullOrEmpty(name)) return false;
            if (ExistProject(name)) return true;

            string sql = string.Format(@"insert into DR_PROJECTNAME (NAME) value ('{0}')", name);
            return MySqlHelper.ExecuteSql(sql) > 0;
        }

        /// <summary>更新项目名称</summary>
        /// <param name="oriName"></param>
        /// <param name="newName"></param>
        /// <returns></returns>
        /// <remarks>不存在原有名称时新建条目</remarks>
        public bool UpdateProject(string oriName, string newName)
        {
            if (string.IsNullOrEmpty(oriName) || string.IsNullOrEmpty(newName)) return false;
            if (!ExistProject(oriName)) return AddProject(newName);

            string sql = string.Format(@"update DR_PROJECTNAME set NAME='{0}' where NAME = '{1}'", oriName, newName);
            return MySqlHelper.ExecuteSql(sql) > 0;
        }

        /// <summary>删除项目名称</summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public bool DeleteProject(string name)
        {
            if (string.IsNullOrEmpty(name)) return false;
            if (!ExistProject(name)) return true;

            string sql = string.Format(@"delete from DR_PROJECTNAME where NAME = '{0}'", name);
            return MySqlHelper.ExecuteSql(sql) > 0;
        }

        /// <summary>获得项目名称列表</summary>
        /// <param name="sort">是否排序，默认排序</param>
        /// <returns></returns>
        public List<string> GetProjectList(bool sort = true)
        {
            string sql = @"select * from DR_PROJECTNAME";
            if (sort) sql += @" order by NAME";

            var dt = MySqlHelper.GetTable(sql);
            if (dt == null || dt.Rows.Count == 0) return null;

            return (from DataRow dr in dt.Rows select dr["NAME"].ToString()).ToList();
        }

        /// <summary>得到项目名称字典</summary>
        /// <returns></returns>
        /// <remarks>键值对类型为Id-Name</remarks>
        public Dictionary<int, string> GetProjectDic()
        {
            string sql = @"select * from DR_PROJECTNAME";

            var dt = MySqlHelper.GetTable(sql);
            if (dt == null || dt.Rows.Count == 0) return null;

            return dt.Rows.Cast<DataRow>().ToDictionary(dr => Convert.ToInt32(dr["ID"]), dr => dr["NAME"].ToString());
        }

        #endregion

        #region 项目类型表

        /// <summary>判断项目类型是否已存在</summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public bool ExistProjectType(string name)
        {
            if (string.IsNullOrEmpty(name)) return false;

            string sql = string.Format(@"select count(*) from DR_PROJECTTYPE where NAME = '{0}'", name);
            var obj = MySqlHelper.GetSingle(sql);
            int res;
            if (obj == null || obj is DBNull || !int.TryParse(obj.ToString(), out res)) return false;
            return res > 0;
        }

        /// <summary>根据项目类型获取ID</summary>
        /// <param name="name"></param>
        /// <param name="donnotCheckExist"></param>
        /// <returns></returns>
        /// <remarks>未找到加入项目类型，错误返回-1</remarks>
        public int GetProjectTypeId(string name, bool donnotCheckExist = false)
        {
            if (string.IsNullOrEmpty(name)) return -1;

            if (donnotCheckExist || ExistProjectType(name))
            {
                string sql = string.Format(@"select ID from DR_PROJECTTYPE where NAME = '{0}' limit 1", name);
                var obj = MySqlHelper.GetSingle(sql);
                int res;
                if (obj == null || obj is DBNull || !int.TryParse(obj.ToString(), out res)) return -1;
                return res;
            }
            else
            {
                string sql = string.Format(@"insert into DR_PROJECTTYPE (NAME) value ('{0}')", name);
                if (MySqlHelper.ExecuteSql(sql) <= 0) return -1;

                return GetProjectTypeId(name, true);
            }
        }

        /// <summary>添加项目类型</summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public bool AddProjectType(string name)
        {
            if (string.IsNullOrEmpty(name)) return false;
            if (ExistProjectType(name)) return true;

            string sql = string.Format(@"insert into DR_PROJECTTYPE (NAME) value ('{0}')", name);
            return MySqlHelper.ExecuteSql(sql) > 0;
        }

        /// <summary>更新项目类型</summary>
        /// <param name="oriName"></param>
        /// <param name="newName"></param>
        /// <returns></returns>
        /// <remarks>不存在原有类型时新建条目</remarks>
        public bool UpdateProjectType(string oriName, string newName)
        {
            if (string.IsNullOrEmpty(oriName) || string.IsNullOrEmpty(newName)) return false;
            if (!ExistProjectType(oriName)) return AddProjectType(newName);

            string sql = string.Format(@"update DR_PROJECTTYPE set NAME='{0}' where NAME = '{1}'", oriName, newName);
            return MySqlHelper.ExecuteSql(sql) > 0;
        }

        /// <summary>删除项目类型</summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public bool DeleteProjectType(string name)
        {
            if (string.IsNullOrEmpty(name)) return false;
            if (!ExistProjectType(name)) return true;

            string sql = string.Format(@"delete from DR_PROJECTTYPE where NAME = '{0}'", name);
            return MySqlHelper.ExecuteSql(sql) > 0;
        }

        /// <summary>获得项目类型列表</summary>
        /// <param name="sort">是否排序，默认排序</param>
        /// <returns></returns>
        public List<string> GetProjectTypeList(bool sort = true)
        {
            string sql = @"select * from DR_PROJECTTYPE";
            if (sort) sql += @" order by NAME";

            var dt = MySqlHelper.GetTable(sql);
            if (dt == null || dt.Rows.Count == 0) return null;

            return (from DataRow dr in dt.Rows select dr["NAME"].ToString()).ToList();
        }

        /// <summary>得到项目类型字典</summary>
        /// <returns></returns>
        /// <remarks>键值对类型为Id-Name</remarks>
        public Dictionary<int, string> GetProjectTypeDic()
        {
            string sql = @"select * from DR_PROJECTTYPE";

            var dt = MySqlHelper.GetTable(sql);
            if (dt == null || dt.Rows.Count == 0) return null;

            return dt.Rows.Cast<DataRow>().ToDictionary(dr => Convert.ToInt32(dr["ID"]), dr => dr["NAME"].ToString());
        }

        #endregion

        #region 待办事项表

        public bool AddTodo(out int id, string userName, string text, string finishDate, bool finished, string remark = null)
        {
            id = -1;

            if (string.IsNullOrEmpty(userName) || string.IsNullOrEmpty(text) ||
                string.IsNullOrEmpty(finishDate))
                return false;

            string userId = GetUserId(userName);
            if (string.IsNullOrEmpty(userId)) return false;
            var dateToday = DateTime.Now.ToString("yyyy-MM-dd");
            int status = finished ? 2 : 1;
            string sql =
                string.Format(
                    @"INSERT INTO DR_TODO (ID_USER,REC_TEXT,REL_DATE,FINISH_DATE,STATUS,REMARK) VALUE ({0},'{1}','{2}','{3}',{4},'{5}')",
                    userId, text, dateToday, finishDate, status, remark);

            if (MySqlHelper.ExecuteSql(sql) <= 0) return false;

            sql =
                string.Format(
                    "select ID from DR_TODO where ID_USER={0} and REC_TEXT='{1}' and REL_DATE='{2}' and FINISH_DATE='{3}' order by ID desc limit 1",
                    userId, text, dateToday, finishDate);
            var res = MySqlHelper.GetSingle(sql);
            id = Convert.ToInt32(res);

            return true;
        }

        public bool UpdateTodo(int id, string text, string finishDate, bool finished, string remark = null)
        {
            if (string.IsNullOrEmpty(text) || string.IsNullOrEmpty(finishDate) || id < 0)
                return false;

            var dateToday = DateTime.Now.ToString("yyyy-MM-dd");
            int status = finished ? 2 : 1;
            string sql =
                string.Format(
                    @"UPDATE DR_TODO SET REC_TEXT='{0}',FINISH_DATE='{1}',LAST_FINISH_DATE='{2}',STATUS={3},REMARK='{4}' WHERE ID={5}",
                    text, finishDate, dateToday, status, remark, id);

            return MySqlHelper.ExecuteSql(sql) > 0;
        }

        public bool UpdateChecked(int id, bool finished)
        {
            var dateToday = DateTime.Now.ToString("yyyy-MM-dd");
            int status = finished ? 2 : 1;
            string sql = string.Format(@"UPDATE DR_TODO SET STATUS={0},LAST_FINISH_DATE='{1}' WHERE ID={2}", status,
                dateToday, id);

            return MySqlHelper.ExecuteSql(sql) > 0;
        }

        public bool DeleteTodo(int id)
        {
            if (id < 0) return false;

            string sql = string.Format(@"DELETE FROM DR_TODO WHERE ID={0}", id);

            return MySqlHelper.ExecuteSql(sql) > 0;
        }

        public DataTable GetAllTodos(string userName)
        {
            if (string.IsNullOrEmpty(userName))
                return null;

            string userId = GetUserId(userName);
            if (string.IsNullOrEmpty(userId)) return null;

            string sql = string.Format(@"SELECT * FROM DR_TODO WHERE ID_USER={0} ORDER BY REL_DATE", userId);

            var dt = MySqlHelper.GetTable(sql);
            return dt;
        }

        /// <summary>得到超时的待办事项列表</summary>
        /// <param name="userName">用户名</param>
        /// <returns>超时的待办事项列表，失败或未找到均返回null</returns>
        public List<JsonTodo> GetOvertimeTodos(string userName)
        {
            if (string.IsNullOrEmpty(userName))
                return null;

            string userId = GetUserId(userName);
            if (string.IsNullOrEmpty(userId)) return null;

            var dateToday = DateTime.Now.ToString("yyyy-MM-dd");
            string sql = string.Format(@"SELECT * FROM DR_TODO WHERE ID_USER={0} AND STATUS<>2 AND FINISH_DATE <= '{1}'", userId, dateToday);

            var dt = MySqlHelper.GetTable(sql);
            if (dt == null || dt.Rows.Count == 0) return null;

            var list = new List<JsonTodo>();
            try
            {
                foreach (DataRow dr in dt.Rows)
                {
                    DateTime dtFinishDate = Convert.ToDateTime(dr["FINISH_DATE"]);
                    JsonTodo todo = new JsonTodo()
                    {
                        Id = Convert.ToInt32(dr["ID"]),
                        Text = dr["REC_TEXT"].ToString(),
                        FinishDate = dtFinishDate.ToString("yyyy-MM-dd"),
                        Finished = false
                    };
                    list.Add(todo);
                }
                return list;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(nameof(GetOvertimeTodos), ex);
                return null;
            }
        }

        #endregion

        #region 每周计划表

        /// <summary>
        /// 获取下周的周一的日期字符串
        /// </summary>
        /// <returns></returns>
        internal string GetMondayStr()
        {
            DateTime date = DateTime.Now;
            switch (date.DayOfWeek)
            {
                case DayOfWeek.Tuesday:
                    date = date.AddDays(-1);
                    break;
                case DayOfWeek.Wednesday:
                    date = date.AddDays(-2);
                    break;
                case DayOfWeek.Thursday:
                    date = date.AddDays(-3);
                    break;
                case DayOfWeek.Friday:
                    date = date.AddDays(-4);
                    break;
                case DayOfWeek.Saturday:
                    date = date.AddDays(-5);
                    break;
                case DayOfWeek.Sunday:
                    date = date.AddDays(-6);
                    break;
                case DayOfWeek.Monday:
                    break;
                default:
                    break;
            }
            date = date.AddDays(7);

            return date.ToString("yyyy-MM-dd");
        }

        public bool ExistWeekPlan(string userName, string date)
        {
            if (string.IsNullOrEmpty(userName) || string.IsNullOrEmpty(date)) return false;
            string userId = GetUserId(userName);
            if (string.IsNullOrEmpty(userId)) return false;

            DateTime dt;
            if (!DateTime.TryParse(date, out dt)) return false;
            date = dt.ToString("yyyy-MM-dd");

            string sql = string.Format(@"select count(*) from dr_weekplan where ID_USER = '{0}' AND REL_DATE='{1}'",
                userId, date);
            var obj = MySqlHelper.GetSingle(sql);
            int res;
            if (obj == null || obj is DBNull || !int.TryParse(obj.ToString(), out res)) return false;
            return res > 0;
        }

        public bool AddWeekPlan(string userName, string text)
        {
            if (string.IsNullOrEmpty(userName) || text == null)
                return false;

            string userId = GetUserId(userName);
            if (string.IsNullOrEmpty(userId)) return false;

            if (text != string.Empty)
            {
                var lines = text.Split(',');
                int count = lines.Count(l => l.Equals(string.Empty));
                if (count > 0)
                {
                    StringBuilder sb = new StringBuilder();
                    foreach (string line in lines)
                    {
                        if (!string.IsNullOrEmpty(line))
                            sb.Append(line + ",");
                    }
                    text = lines.ToString();
                    if (text.EndsWith(",")) text = text.Substring(0, text.Length - 1);
                }
            }

            var dateMonday = GetMondayStr();
            string sql =
                string.Format(@"insert into dr_weekplan (ID_USER,REL_DATE,STATUS,TEXT) VALUE ({0},'{1}',{2},'{3}')",
                    userId, dateMonday, 1, text);

            return MySqlHelper.ExecuteSql(sql) > 0;
        }

        public bool UpdateWeekPlan(string userName, string text, string date)
        {
            if (string.IsNullOrEmpty(userName) || string.IsNullOrEmpty(text))
                return false;

            string userId = GetUserId(userName);
            if (string.IsNullOrEmpty(userId)) return false;

            if (string.IsNullOrEmpty(date))
            {
                date = GetMondayStr();
            }
            else
            {
                DateTime dt;
                if (!DateTime.TryParse(date, out dt)) return false;
                date = dt.ToString("yyyy-MM-dd");
            }

            var lines = text.Split(',');
            int count = lines.Count(l => l.Equals(string.Empty));
            if (count > 0)
            {
                StringBuilder sb = new StringBuilder();
                foreach (string line in lines)
                {
                    if (!string.IsNullOrEmpty(line))
                        sb.Append(line + ",");
                }
                text = lines.ToString();
                if (text.EndsWith(",")) text = text.Substring(0, text.Length - 1);
            }

            string sql = string.Format(@"UPDATE dr_weekplan SET TEXT='{0}' WHERE ID_USER={1} AND REL_DATE='{2}'",
                text, userId, date);

            return MySqlHelper.ExecuteSql(sql) > 0;
        }

        public bool DeleteWeekPlan(string userName, string date)
        {
            if (string.IsNullOrEmpty(userName) || string.IsNullOrEmpty(date))
                return false;

            string userId = GetUserId(userName);
            if (string.IsNullOrEmpty(userId)) return false;

            DateTime dt;
            if (!DateTime.TryParse(date, out dt)) return false;
            date = dt.ToString("yyyy-MM-dd");

            string sql = string.Format(@"DELETE FROM dr_weekplan WHERE ID_USER={0} AND REL_DATE='{1}'", userId, date);

            return MySqlHelper.ExecuteSql(sql) > 0;
        }

        public DataTable GetWeekPlan(string userName, string date)
        {
            if (string.IsNullOrEmpty(userName))
                return null;

            string userId = GetUserId(userName);
            if (string.IsNullOrEmpty(userId)) return null;

            if (string.IsNullOrEmpty(date))
            {
                date = GetMondayStr();
            }
            else
            {
                DateTime dtime;
                if (!DateTime.TryParse(date, out dtime)) return null;
                date = dtime.ToString("yyyy-MM-dd");
            }

            string sql = string.Format(@"SELECT * FROM dr_weekplan WHERE ID_USER={0} AND REL_DATE='{1}'", userId, date);

            var dt = MySqlHelper.GetTable(sql);
            return dt;
        }

        public DataTable GetAllWeekPlans(string userName)
        {
            if (string.IsNullOrEmpty(userName))
                return null;

            string userId = GetUserId(userName);
            if (string.IsNullOrEmpty(userId)) return null;

            string sql = string.Format(@"SELECT * FROM dr_weekplan WHERE ID_USER={0} ORDER BY REL_DATE", userId);

            var dt = MySqlHelper.GetTable(sql);
            return dt;
        }

        #endregion

    }
}